
/**
	AUTHOR		: VEERU
	DATE		: OCT 26th 2005
	DESCRIPTION	: THIS PROCEDURE IS USED FOR ASSEMBLY LOT DATA DUMP REPORT.
	USAGE           : EXEC SP_ASSEMBLY_DUMP_REPORT '07/18/2005', '07/25/2005'			  
*/

ALTER PROC SP_ASSEMBLY_DUMP_REPORT

@FROM_DATE DATETIME,
@TO_DATE  DATETIME

AS
BEGIN

	DECLARE @ASSM_REL_ID 	INT
	DECLARE @SORT_REL_ID    INT
	DECLARE @CS_LOT_ID      INT
	DECLARE @QTY_START      NUMERIC
	DECLARE @SORT_WAFER_ID  VARCHAR(300)
	
	DECLARE @SORT_REL_NUMB 	VARCHAR(50)
	DECLARE @SORT_REL_DATE 	VARCHAR(50)
	DECLARE @SORT_REL_PO	VARCHAR(50)
	DECLARE @PROCESSSTEP_ID	BIGINT
	DECLARE @VENDOR_NAME	VARCHAR(50)
	DECLARE @STATUS		VARCHAR(50)
	DECLARE @LOT_ID		INT
	DECLARE @LOT_NUMBER	VARCHAR(50)
	DECLARE @QTY_AVAIL	INT
	DECLARE @QTY_PULLED	INT
	DECLARE @ASSM_WAFER_ID	INT
	DECLARE @PART_NUMBER	VARCHAR(50)
	DECLARE @BASE_PRODUCT 	VARCHAR(50)
	DECLARE @PS_TYPE 	VARCHAR(50)
	DECLARE @WAFER_ID 	VARCHAR(200)
	DECLARE @TOT_BIN_INFO 	VARCHAR(200)	
	DECLARE @YIELD 		FLOAT
	DECLARE @BIN_REJECTED   INT
	DECLARE @DATE_OUT	DATETIME
	DECLARE @SORT_LOT_WAFER_ID VARCHAR(300)
	DECLARE @BIN_INFO 	   VARCHAR (100)
	DECLARE @FAB_LOT_NO        VARCHAR (50)
	DECLARE @SORT_LOCATION     VARCHAR (50)
	DECLARE @SORT_REV 	   VARCHAR (50)
	DECLARE @ASSM_LOT_NUMBER   VARCHAR(50)
	DECLARE @TEST_LOT_ID 	   INT
	DECLARE @TST_QTY_OUT 	   NUMERIC
	DECLARE @TST_DATE_OUT 	   DATETIME
	
	DECLARE @MAX_LI_DATE 	   DATETIME
 	DECLARE	@MIN_LI_DATE 	   DATETIME 	
 	DECLARE @LI_DATE_FROM 	   DATETIME
 	DECLARE	@LI_DATE_TO 	   DATETIME

	DECLARE @WAFER_IDS_TABLE      TABLE ( SORT_REL_ID INT, LOT_WAFER_ID INT )
	DECLARE @ASSM_WAFER_IDS_TABLE TABLE ( LOT_WAFER_ID INT )
	DECLARE @BIN_INFO_TABLE       TABLE ( BIN_INFO VARCHAR(200) )

	DECLARE @RESULTS TABLE( 
				ASSEMBLY_RELEASE_ID     INT,
				[ASSM LOT NO] 		VARCHAR(50),
				[PKG TYPE]    		VARCHAR(50) ,
				[QTY START]   		INT ,
				[OPN]         		VARCHAR(50),
				[FAB LOT NO]		VARCHAR(50),
				LOT_ID   		INT ,
				WAFER_ID    		VARCHAR(200),
				[BIN INFO]		VARCHAR(200),
				[SORT REV ]    		VARCHAR(50),
				[SORT LOCATION] 	VARCHAR(50),
				[SORT YIELD]    	FLOAT,
				[ASSM DATE IN ] 	DATETIME,
				[TEST DATE OUT] 	DATETIME,
				BIN_INFO		VARCHAR (100),
				[ASSM SPEC]  		VARCHAR(50),
				[ASSM REV]  		VARCHAR(50),
				[TEST SPEC] 		VARCHAR(50),
				[TEST REV]		VARCHAR(50),
				[TEST PROG NAME]	VARCHAR(50),
				[TEST PROG REV] 	VARCHAR(50),
				[TEST YIELD] 		FLOAT,
				[ASSM IN / TEST OUT] 	VARCHAR(50))

	DECLARE @RESULTS_TABLE TABLE (  
					[ASSM LOT NO] 		VARCHAR(50),
					[PKG TYPE]    		VARCHAR(50) ,
					[QTY START]   		INT ,
					[OPN]         		VARCHAR(50),
					[FAB LOT NO]		VARCHAR(50),
					[WAFER NO]    		VARCHAR(200),
					[BIN INFO]		VARCHAR(200),
					[SORT REV ]    		VARCHAR(50) ,
					[SORT LOCATION] 	VARCHAR(50),
					[SORT YIELD]    	FLOAT,
					[ASSM DATE IN ] 	DATETIME,
					[TEST DATE OUT] 	DATETIME,
					[ASSM SPEC]  		VARCHAR(50),
					[ASSM REV]  		VARCHAR(50),
					[TEST SPEC] 		VARCHAR(50),
					[TEST REV]		VARCHAR(50),
					[TEST PROG NAME]	VARCHAR(50),
					[TEST PROG REV] 	VARCHAR(50),
					[TEST YIELD] 		FLOAT,
					[ASSM IN / TEST OUT] 	VARCHAR(50) )
	DECLARE @WAFERCURSOR CURSOR

	SELECT @MAX_LI_DATE = MAX( CONVERT( DATETIME, ASSEMBLY_RELEASE_DATE ) ) FROM ASSM_RELEASE_TABLE
 	SELECT @MIN_LI_DATE = MIN( CONVERT( DATETIME, ASSEMBLY_RELEASE_DATE ) ) FROM ASSM_RELEASE_TABLE	
 	
	IF ( @FROM_DATE IS NULL) AND (@TO_DATE IS NULL) 
 	BEGIN
 		SET @LI_DATE_TO = @MAX_LI_DATE
		SET @LI_DATE_FROM = DATEADD( MONTH, -3, @MAX_LI_DATE )
 	END
 	ELSE IF (@FROM_DATE IS NOT NULL ) AND (@TO_DATE IS NOT NULL ) 
 	BEGIN
 		SET @LI_DATE_TO = @TO_DATE
 		SET @LI_DATE_FROM = @FROM_DATE
 	END	
 	ELSE IF (@FROM_DATE IS NULL) AND (@TO_DATE IS NOT NULL ) 
 	BEGIN
 		SET @LI_DATE_TO = @TO_DATE
 		SET @LI_DATE_FROM = @MIN_LI_DATE
 	END 
  	ELSE IF (@FROM_DATE IS NOT NULL ) AND (@TO_DATE IS NULL) 
 	BEGIN
 		SET @LI_DATE_TO = @MAX_LI_DATE
 		SET @LI_DATE_FROM = @FROM_DATE	
 	END
 	ELSE
 	BEGIN
 		SET @LI_DATE_TO = @MAX_LI_DATE
 		SET @LI_DATE_FROM = @MIN_LI_DATE
 	END	

	INSERT INTO @RESULTS SELECT ART.ASSEMBLY_RELEASE_ID, ART.ASSEMBLY_RELEASE_NO AS [ASSM LOT NO], ART.PACKAGE_TYPE AS [PKG TYPE], NULL AS [QTY START],
				P.PART_NUMBER AS [OPN], ART.LOT_NUMBER, NULL, NULL, NULL, NULL, NULL, NULL, 
				ASSEMBLY_RELEASE_DATE AS [ASSM DATE IN ], NULL, NULL, 
				ST1.SPEC_FILE_NAME AS [ASSM SPEC] , ST1.REVISION AS [ASSM REV],	ST2.SPEC_FILE_NAME AS [TEST SPEC] , ST2.REVISION AS [TEST REV],
				ST2.POST_ASSEMBLY_TEST [TEST PROG NAME], ST2.POST_ASSEMBLY_TEST_REV [TEST PROG REV], NULL, NULL  
			FROM PRODUCT P, SPEC_TABLE ST1 , SPEC_TABLE ST2 RIGHT OUTER JOIN ASSM_RELEASE_TABLE ART ON ST2.SPECFILE_ID = ART.TEST_SPEC_FILE_ID
			WHERE P.PROCESSSTEP_ID = ART.PROCESSSTEP_ID 
				AND ST1.SPECFILE_ID = ART.ASSEMBLY_SPEC_FILE_ID
				AND CONVERT(DATETIME, ART.ASSEMBLY_RELEASE_DATE ) >= @LI_DATE_FROM AND CONVERT( DATETIME, ART.ASSEMBLY_RELEASE_DATE ) <= @LI_DATE_TO 				
				AND ART.STATUS <> 'CANCELLED' 				
				ORDER BY ASSEMBLY_RELEASE_NO 

	SELECT TOP 1 @ASSM_REL_ID = ASSEMBLY_RELEASE_ID,  @ASSM_LOT_NUMBER = [ASSM LOT NO] FROM @RESULTS
	WHILE ( @@ROWCOUNT > 0 )
	BEGIN	
		-- TO FORM WAFER IDS AS A SINGLE STRING FOR THE SINGE ASSEMBLY RELEASE
		SET @WAFER_ID = ''
		DELETE FROM @ASSM_WAFER_IDS_TABLE
		INSERT INTO @ASSM_WAFER_IDS_TABLE SELECT WAFER_ID FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSM_REL_ID 		
		SELECT TOP 1 @ASSM_WAFER_ID = LOT_WAFER_ID FROM @ASSM_WAFER_IDS_TABLE
		WHILE @@ROWCOUNT > 0 
		BEGIN
			SET @WAFER_ID = @WAFER_ID + CONVERT( VARCHAR, @ASSM_WAFER_ID )+ ','
			DELETE FROM @ASSM_WAFER_IDS_TABLE WHERE LOT_WAFER_ID = @ASSM_WAFER_ID
			SELECT TOP 1 @ASSM_WAFER_ID = LOT_WAFER_ID FROM @ASSM_WAFER_IDS_TABLE
		END	
		-- TO FORM BINNING INFO AS A SINGLE STRING FOR THE SINGE ASSEMBLY RELEASE
		SET @TOT_BIN_INFO = ''
		DELETE FROM @BIN_INFO_TABLE
		INSERT INTO @BIN_INFO_TABLE SELECT DISTINCT(REPLACE( REPLACE( BIN_INFO, 'BIN', '' ), '&', ',' )) FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSM_REL_ID
		SELECT TOP 1 @BIN_INFO = BIN_INFO FROM @BIN_INFO_TABLE
		WHILE @@ROWCOUNT > 0 
		BEGIN
			SET @TOT_BIN_INFO = @TOT_BIN_INFO + CONVERT( VARCHAR, @BIN_INFO )+ ','
			DELETE FROM @BIN_INFO_TABLE WHERE BIN_INFO = @BIN_INFO
			SELECT TOP 1 @BIN_INFO = BIN_INFO FROM @BIN_INFO_TABLE
		END				

		DELETE FROM @BIN_INFO_TABLE
		INSERT INTO @BIN_INFO_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @TOT_BIN_INFO )
		SET @TOT_BIN_INFO = ''
		SELECT TOP 1 @BIN_INFO = BIN_INFO FROM @BIN_INFO_TABLE
		WHILE @@ROWCOUNT > 0 
		BEGIN
			SET @TOT_BIN_INFO = @TOT_BIN_INFO + CONVERT( VARCHAR, @BIN_INFO )+ ','
			DELETE FROM @BIN_INFO_TABLE WHERE BIN_INFO = @BIN_INFO
			SELECT TOP 1 @BIN_INFO = BIN_INFO FROM @BIN_INFO_TABLE
		END	

		SELECT @FAB_LOT_NO = LT.LOT_NUMBER FROM LOT_TABLE LT, ASSM_RELEASE_LOT_TABLE ARLT WHERE LT.LOT_ID = ARLT.LOT_ID AND ASSEMBLY_RELEASE_ID = @ASSM_REL_ID
		SELECT @QTY_START = SUM (QTY_PULLED) FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSM_REL_ID
		
		IF ( @FAB_LOT_NO IS NOT NULL )
		BEGIN 
			DELETE FROM @WAFER_IDS_TABLE
			SET @WAFERCURSOR = CURSOR FOR SELECT SORT_REL_ID, WAFER_ID FROM SORT_RELEASE_LOT_TABLE WHERE LOT_NUMBER = @FAB_LOT_NO
			OPEN @WAFERCURSOR
			FETCH NEXT FROM @WAFERCURSOR INTO @SORT_REL_ID, @SORT_WAFER_ID
			WHILE @@FETCH_STATUS = 0
			BEGIN			
				SET @SORT_LOT_WAFER_ID = REPLACE( @SORT_WAFER_ID , ';', ',' )
				INSERT INTO @WAFER_IDS_TABLE SELECT @SORT_REL_ID, PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @SORT_LOT_WAFER_ID )
				SET @SORT_REL_ID = NULL 
				SET @SORT_WAFER_ID = NULL 
				FETCH NEXT FROM @WAFERCURSOR INTO @SORT_REL_ID, @SORT_WAFER_ID
			END		
			CLOSE @WAFERCURSOR
			DEALLOCATE @WAFERCURSOR
			SELECT @SORT_REL_ID = SORT_REL_ID FROM @WAFER_IDS_TABLE WHERE LOT_WAFER_ID IN ( SELECT WAFER_ID FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSM_REL_ID )			
		END
		SELECT @SORT_LOCATION = LCT.L_VENDOR_NAME/*, @SORT_REV = SRT.SORT_REVISION*/ FROM SORT_RELEASE_TABLE SRT, LOCATION LCT WHERE LCT.VENDOR_ID = SRT.VEND_ID AND LCT.L_TYPE = 'SORT' AND SRT.SORT_REL_ID = @SORT_REL_ID 
		SELECT @SORT_REV = ST.REVISION FROM SORT_RELEASE_TABLE SRT, SPEC_TABLE ST WHERE SRT.SPEC_FILE_ID = ST.SPECFILE_ID AND SRT.SORT_REL_ID = @SORT_REL_ID 
		SELECT @YIELD = AVG( YIELD ) , @BIN_REJECTED = SUM( BIN_REJECTED ) 
		FROM INFORMATIVE_BINNING IB WHERE IB.LOT_ID IN ( SELECT LOT_ID FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSM_REL_ID  ) AND WAFER_ID IN ( SELECT WAFER_ID FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSM_REL_ID )
		
		SELECT @TEST_LOT_ID = LT.LOT_ID FROM LOT_TABLE LT, PROCESS_STEP PS WHERE LT.PROCESSSTEP_ID =  PS.PROCESSSTEP_ID AND PS.PS_TYPE_NAME = 'TEST' AND LT.LOT_NUMBER = @ASSM_LOT_NUMBER
		SELECT @CS_LOT_ID = LT.LOT_ID FROM LOT_TABLE LT, PROCESS_STEP PS WHERE LT.PROCESSSTEP_ID =  PS.PROCESSSTEP_ID AND PS.PS_TYPE_NAME = 'Class Store' AND LT.LOT_NUMBER = @ASSM_LOT_NUMBER
		
		-- TO GET TEST QTY OUT AND TEST QTY OUT
		SELECT @TST_DATE_OUT = SHIP_DATE, @TST_QTY_OUT = SHIP_QTY FROM shiPpED_loT WHERE LOT_ID = @TEST_LOT_ID
		IF @TST_QTY_OUT IS NULL OR @TST_QTY_OUT = 0
		BEGIN			
			SELECT @TST_QTY_OUT = IL_QTY_IN, @TST_DATE_OUT = IL_DATE_IN  FROM INVENTORY_LOT WHERE LOT_ID = @CS_LOT_ID
			-- GETTING QTY IN FOR CLASS STORE AS TEST QTY OUT AND DATE IN AS TEST DATE OUT 
			IF @TST_QTY_OUT IS NULL OR @TST_QTY_OUT = 0
			BEGIN
				SELECT TOP 1 @TST_QTY_OUT = IL_QTY_IN, @TST_DATE_OUT = IL_DATE_IN  FROM INVENTORY_LOT_HISTORY WHERE LOT_ID = @CS_LOT_ID ORDER BY TIMESTAMP DESC
			END
		END
		INSERT INTO @RESULTS_TABLE			
			SELECT  [ASSM LOT NO], [PKG TYPE], @QTY_START, [OPN],@FAB_LOT_NO, @WAFER_ID, 'Bin'+SUBSTRING( @TOT_BIN_INFO, 1, LEN(@TOT_BIN_INFO)-1),  @SORT_REV, @SORT_LOCATION, @YIELD, [ASSM DATE IN ], @TST_DATE_OUT, [ASSM SPEC], [ASSM REV], [TEST SPEC], [TEST REV], [TEST PROG NAME], [TEST PROG REV], @TST_QTY_OUT*100/@QTY_START, CONVERT(VARCHAR,@QTY_START)+'/'+CONVERT(VARCHAR,@TST_QTY_OUT)
			FROM @RESULTS WHERE ASSEMBLY_RELEASE_ID = @ASSM_REL_ID 
	

		SET @LOT_NUMBER  	= NULL
		SET @WAFER_ID 		= NULL
		SET @PROCESSSTEP_ID  	= NULL
		SET @TOT_BIN_INFO 	= NULL
		SET @FAB_LOT_NO       	= NULL
		SET @SORT_REV		= NULL
		SET @SORT_LOCATION	= NULL
		SET @YIELD		= NULL
		SET @ASSM_LOT_NUMBER	= NULL
		SET @TEST_LOT_ID	= NULL		
		SET @TST_QTY_OUT	= NULL
		SET @TST_DATE_OUT 	= NULL
		SET @FAB_LOT_NO 	= NULL
		SET @QTY_START		= NULL
		SET @TEST_LOT_ID	= NULL
		SET @CS_LOT_ID		= NULL
		SET @SORT_REL_ID	= NULL

		DELETE FROM @RESULTS WHERE ASSEMBLY_RELEASE_ID = @ASSM_REL_ID 
		SELECT TOP 1 @ASSM_REL_ID = ASSEMBLY_RELEASE_ID,  @ASSM_LOT_NUMBER = [ASSM LOT NO] FROM @RESULTS
	END 

	SELECT * FROM @RESULTS_TABLE
END